﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Data.SqlClient;
using DataAccessLayer.Properties;
using DataAccessLayer.Classes;
using DTO;
using IMModel;
using DTO;


//Класс реализует доступ к данным с помощью Linq2Sql

namespace DataAccessLayer
{
    using DTO = DTO;
    using System.ServiceModel;
    using System.Security.Principal;
    using DataAccessLayer.Classes;
    using System.Diagnostics.Contracts;
    using IMModel.Domain;
    using DTO.Reports;
    using IMModel.Domain.Common;
    
    public partial class SqlDataProvider : IDataProvider
    {
        public IEnumerable<DTO.Term> GetTerms()
        {
            using (IMModel.Domain.IMDataDataContext db = 
                new IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                //извлекаем из БД все периоды
                return db.Terms.Select(x => DTOConverter.Instance.CreateTerm(x)).ToList();
            }
        }

        public IEnumerable<DTO.Organization> GetOrganizations()
        {
            using (IMModel.Domain.IMDataDataContext db =
                new IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                //извлекаем из БД все периоды
                return db.Organizations.Select(x => DTOConverter.Instance.CreateOrganization(x)).ToList();
            }
        }

        public IEnumerable<DTO.Period> GetPeriods()
        {
            using (IMModel.Domain.IMDataDataContext db =
                new IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                //извлекаем из БД все периоды
                return db.Periods.Select(x => DTOConverter.Instance.CreatePeriod(x)).ToList();
            }
        }

        public IEnumerable<DTO.Section> GetSectionForTree(int TermID)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                return db.SectionTerms.Where(x => x.IDTerm == TermID && x.SectionTerm1 == null).AsEnumerable().
                    OrderBy(x => x.Order, new NatSort<String>()).
                    Select(x => DTOConverter.Instance.CreateSection(x)).ToList();
            }
        }

        public IEnumerable<DTO.RespondentAnswers> GetRespAnswers(int IDCharTerm, Nullable<int> IDDep)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var t = db.CharacteristicTerms.Single(x => x.IDCharacteristicTerm == IDCharTerm).Term;
                //int IDTerm = t.IDTerm;
                var res = t.Respondents.Where(x => !(x.Dismissed || x.IgnoreVolumes));

                if (!IDDep.HasValue)
                {
                    return res.Select(x =>
                        DTOConverter.Instance.CreateRespAnswers(x, IDCharTerm)).ToList();

                    //return db.Respondents.Where(x => x.DepartmentTerm.IDTerm == IDTerm && !x.Dismissed && !x.IgnoreVolumes).Select(x => DTOConverter.Instance.CreateRespAnswers(x, IDCharTerm)).ToList();
                }
                else
                {
                    IMModel.Domain.DepartmentTerm Dep = db.DepartmentTerms.FirstOrDefault(x =>
                        x.IDDepartment == IDDep && x.Term == t);
                    return res.Where(x => Dep.Containts(x)).Select(x =>
                        DTOConverter.Instance.CreateRespAnswers(x, IDCharTerm)).ToList();

                    //return db.Respondents.Where(x => x.DepartmentTerm.IDTerm == IDTerm).AsEnumerable().Where(x=>Dep.Containts(x)).Select(x => DTOConverter.Instance.CreateRespAnswers(x, IDCharTerm)).ToList();
                }
            }
        }

        public IEnumerable<DTO.Permission> GetPermissions(IEnumerable<string> SIDs, int IDTerm)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                List<DTO.Permission> list = new List<DTO.Permission>();
                
                var PermittedChars = (from CT in db.CharacteristicTerms
                                      join CR in db.CharacteristicResponsibles on CT.IDCharacteristicTerm equals CR.IDCharacteristicTerm
                                      join R in db.Responsibles on CR.IDResponsible equals R.IDResponsible
                                      join P in db.Positions on R.IDPosition equals P.IDPosition
                                      where SIDs.Contains(P.SID) && CT.IDTerm == IDTerm
                                      select CT).Distinct().ToList();

                foreach (IMModel.Domain.CharacteristicTerm CharTerm in PermittedChars)
                {
                    var PermittedDeps = (from CR in db.CharacteristicResponsibles
                                        join CT in db.CharacteristicTerms on CR.IDCharacteristicTerm equals CT.IDCharacteristicTerm
                                        join R in db.Responsibles on CR.IDResponsible equals R.IDResponsible
                                        join D in db.Departments on R.IDDepartment equals D.IDDepartment
                                        join DT in db.DepartmentTerms on D.IDDepartment equals DT.IDDepartment
                                        where SIDs.Contains(D.SID) && CT.IDCharacteristicTerm == CharTerm.IDCharacteristicTerm && DT.IDTerm == IDTerm
                                        select DT).Select(x=>DTOConverter.Instance.CreateDepartmentWithNotRespondents(x)).ToList();

                    DTO.Permission Perm = new DTO.Permission
                    {
                        Char = DTOConverter.Instance.CreateCharacteristic(CharTerm),
                        Departments = PermittedDeps
                    };

                    list.Add(Perm);
                }

                return list;
            }
        }

        public bool IsResponsible(int CharTermID, int RespID, IEnumerable<String> SIDs)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                bool flag = IsResponsible(CharTermID, SIDs);

                var PermittedDeps = (from CR in db.CharacteristicResponsibles
                                     join CT in db.CharacteristicTerms on CR.IDCharacteristicTerm equals CT.IDCharacteristicTerm
                                     join R in db.Responsibles on CR.IDResponsible equals R.IDResponsible
                                     join D in db.Departments on R.IDDepartment equals D.IDDepartment
                                     join DT in db.DepartmentTerms on D.IDDepartment equals DT.IDDepartment
                                     where SIDs.Contains(D.SID) && CT.IDCharacteristicTerm == CharTermID
                                     select DT);

                int PermittedPosCount = (from CR in db.CharacteristicResponsibles
                                     join CT in db.CharacteristicTerms on CR.IDCharacteristicTerm equals CT.IDCharacteristicTerm
                                     join R in db.Responsibles on CR.IDResponsible equals R.IDResponsible
                                     join P in db.Positions on R.IDPosition equals P.IDPosition
                                     where SIDs.Contains(P.SID) && CT.IDCharacteristicTerm == CharTermID
                                     select P).Count();

                var Resp = db.Respondents.Single(x=>x.IDRespondent == RespID);

                foreach (IMModel.Domain.DepartmentTerm DT in PermittedDeps)
                {
                    if (DT.Containts(Resp) && flag) return true;
                }

                return PermittedPosCount > 0;

                //return false;

            }
        }

        public DTO.RespondentAnswers GetAnswersByCharAndResp(int IDCharTerm, int RespID)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                //return db.Answers.Where(x => x.IDRespondent == RespID && x.IDCharacteristicTerm == IDCharTerm).Select(x => DTOConverter.Instance.CreateRespAnswers(x.Respondent));

                IMModel.Domain.Respondent R = db.Respondents.Single(x => x.IDRespondent == RespID);
                return DTOConverter.Instance.CreateRespAnswers(R, IDCharTerm);
            }
        }

        public DTO.RespondentAnswers GetAnswersByCharAndPerson(int IDCharTerm, int IDPerson)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                //return db.Answers.Where(x => x.IDRespondent == RespID && x.IDCharacteristicTerm == IDCharTerm).Select(x => DTOConverter.Instance.CreateRespAnswers(x.Respondent));

             //   IMModel.Domain.Respondent R = db.Respondents.Single(x => x.Person.IDPerson == IDPerson);
            //    return DTOConverter.Instance.CreateRespAnswers(R, IDCharTerm);
                List<DTO.Answer> res = new List<DTO.Answer>();
                res = (from R in db.Respondents
                             join P in db.Persons on R.Person.IDPerson equals P.IDPerson
                             join A in db.Answers on R.IDRespondent equals A.IDRespondent
                             where IDPerson == P.IDPerson && IDCharTerm == A.CharacteristicTerm.IDCharacteristicTerm
                             select  DTOConverter.Instance.CreateAnswer(A)).ToList();

                return new RespondentAnswers
                                      {
                                          Answers =res,
                                          State = StateObject.Unchanged

                                      };
            }
        }


        public IEnumerable<DTO.Gradation> GetGradations(int CharTermID)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                return db.CharacteristicGroups.Where(x => x.IDCharacteristicTerm == CharTermID).Select(x => DTOConverter.Instance.CreateGradationGroup(x)).ToList();
            }
        }


        public void SaveAnswers(DTO.RespondentAnswers RA, String UserName)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                //удаленные ответы
                var DeletedAnswers = RA.Answers.Where(x => x.State == DTO.StateObject.Deleted);
                foreach (DTO.Answer A in DeletedAnswers)
                {
                    Answer Answer = new Answer
                    {
                        IDCharacteristicTerm = RA.IDCharTerm,
                        DateTime = DateTime.Now,
                        IDRespondent = RA.Resp.IDRespondent,
                        Volume = (double)A.Volume,
                        UserName = UserName,
                        IsDeleted = true,
                        Answer1 = db.Answers.Single(x => x.IDAnswer == A.IDAnswer) //устанавливаем родителя
                    };

                    AddGradationsToAnswer(A, Answer);

                    db.Answers.InsertOnSubmit(Answer);
                }
                
                //добавленные ответы
                var AddedAnswers = RA.Answers.Where(x => x.State == DTO.StateObject.Created);
                foreach (DTO.Answer A in AddedAnswers)
                {
                    Answer Answer = new Answer
                        {
                            IDCharacteristicTerm = RA.IDCharTerm,
                            DateTime = DateTime.Now,
                            IDRespondent = RA.Resp.IDRespondent,
                            Volume = (double)A.Volume,
                            UserName = UserName,
                            IsDeleted = false,
                            IDOldAnswer = null
                        };

                    AddGradationsToAnswer(A, Answer);

                    db.Answers.InsertOnSubmit(Answer);
                }
                
                //редактированные ответы
                var EditedAnswers = RA.Answers.Where(x => x.State == DTO.StateObject.Updated);
                foreach (DTO.Answer A in EditedAnswers)
                {
                    Answer Answer = new Answer
                    {
                        IDCharacteristicTerm = RA.IDCharTerm,
                        DateTime = DateTime.Now,
                        IDRespondent = RA.Resp.IDRespondent,
                        Volume = (double)A.Volume,
                        UserName = UserName,
                        IsDeleted = false,
                        Answer1 = db.Answers.Single(x=>x.IDAnswer == A.IDAnswer) //устанавливаем родителя
                    };

                    AddGradationsToAnswer(A, Answer);

                    db.Answers.InsertOnSubmit(Answer);
                }
                //------------------


                db.SubmitChanges();
            }
            
            
        }

        /// <summary>
        /// добавляет градации в Answer
        /// </summary>
        /// <param name="A">DTO-объект</param>
        /// <param name="Answer">Объект Linq2Sql</param>
        private void AddGradationsToAnswer(DTO.Answer A, Answer Answer)
        {
            foreach (DTO.Gradation Grad in A.Gradations)
            {
                if (Grad.ID != 0)
                {
                    Answer.GradationsAnswers.Add(new GradationsAnswer
                    {
                        Answer = Answer,
                        Volume = Grad.Volume,
                        IDGradationTerm = Grad.ID
                    });
                }
            }
        }

        public string GetSID(int IDChar, int IDTerm)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                return (from Pos in db.Positions
                        join Res in db.Responsibles on Pos.IDPosition equals Res.IDPosition
                        join CR in db.CharacteristicResponsibles on Res.IDResponsible equals CR.IDResponsible
                        join CT in db.CharacteristicTerms on CR.IDCharacteristicTerm equals CT.IDCharacteristicTerm
                        where (CT.IDCharacteristic == IDChar && CT.IDTerm == IDTerm)
                        select Pos).FirstOrDefault().SID;
            }
        }

        public string GetSupervisorSID()
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                return (from Pos in db.Positions
                        where (Pos.Name == "Супервайзер")
                        select Pos).FirstOrDefault().SID;
            }
        }

        public IEnumerable<DTO.Department> GetDepartmentsList(int IDTerm)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                //берем только кафедры и факультеты
                return db.DepartmentTerms.Where(x => x.DepartmentTerm1 != null && x.IDTerm == IDTerm).Select(x => DTOConverter.Instance.CreateDepartmentWithNotRespondents(x)).ToList();
            }
        }

        public IEnumerable<DTO.IndexRow> GetIndexes(int TermID, IEnumerable<int> Cats, 
            IEnumerable<int> Chars, IEnumerable<int> Respondents, 
            DTO.IndexListType Type, DTO.IndexStrategyType strategy)
        {
            using (IMDataDataContext db = 
                new IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var term = db.Terms.Where(x => x.IDTerm == TermID).Single();
                CDebug.Write(strategy.ToString());
                CDebug.Write(GetContext(term, strategy).Policy.ToString());
                CDebug.Write(term.ToString());
                RespondentIndexCollection result = term.CalcIndexes(GetContext(term, strategy)); //.Indexes;
                CDebug.Write(string.Join(", ", result.Select(x => x.Key.Person.LastName)));
                CDebug.Write("||||||||||||||||||||||||||||||||||||||||");

                List<DTO.IndexRow> indexes = new List<IndexRow>();

                foreach (KeyValuePair<IMModel.Domain.Respondent, double> Res in result)
                {
                    DTO.IndexRow Row = DTOConverter.Instance.CreateIndexesStub(Res.Key, Res.Value, Type);
                    indexes.Add(Row);
                }

                CDebug.Write(string.Join(", ", indexes.Select(x=>x.Resp.Person.LastName)));
                return indexes.OrderByDescending(x => x.Index);
            }
        }

        //private RespondentIndexCollection CalcIndexes(Term term, DTO.IndexStrategyType strategy)
        //{
        //    switch (strategy)
        //    {
        //        case DTO.IndexStrategyType.PPS: return term.CalcIndexes(term.GetIndexContextPPS());
        //        case DTO.IndexStrategyType.Doctor: return term.CalcIndexes(term.GetIndexContextDoctors());
        //        default: return term.CalcIndexes(term.GetIndexContextDefault());
        //    }
        //}

        private IndexContext GetContext(Term term, DTO.IndexStrategyType strategy)
        {
            switch (strategy)
            {
                case DTO.IndexStrategyType.PPS: return term.GetIndexContextPPS();
                case DTO.IndexStrategyType.Doctor: return term.GetIndexContextDoctors();
                default: return term.GetIndexContextDefault();
            }
        }


        public IEnumerable<string> GetRoles(IList<string> ListSID)
        {
            using (IMModel.Domain.IMDataDataContext db = 
                new IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var list = db.Positions.ToList().Where(x=>ListSID.Contains(x.SID));

                var Deplist = db.Departments.ToList().Where(x => ListSID.Contains(x.SID));

                List<string> Roles = new List<string>();
                foreach (Position Pos in list)
                {
                    String DepName = "";
                    if (Pos.IsTotal != true)
                    {
                        foreach (Department Dep in Deplist)
                        {
                            DepName = Dep.Abbriviation;
                        }
                    }
                    Roles.Add(Pos.Name + " " + DepName);
                }

                return Roles;
            }
        }

        public bool IsResponsible(int IDCharTerm, IEnumerable<String> SIDs)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                return (from CT in db.CharacteristicTerms
                        join CR in db.CharacteristicResponsibles on CT.IDCharacteristicTerm equals CR.IDCharacteristicTerm
                        join Resp in db.Responsibles on CR.IDResponsible equals Resp.IDResponsible
                        join Pos in db.Positions on Resp.IDPosition equals Pos.IDPosition
                        where (SIDs.Contains(Pos.SID) && CT.IDCharacteristicTerm == IDCharTerm)
                        select CT).Count() > 0;
            }
        }

        public bool IsVisibleForAll(int IDCharTerm)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                return db.CharacteristicTerms.Where(CT => CT.IDCharacteristicTerm == IDCharTerm).FirstOrDefault().Visibility;
            }
        }

        public void AddInputLog(String UserName)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                InputLog IL = new InputLog();
                IL.Date = DateTime.Now;
                IL.UserName = UserName;
                db.InputLogs.InsertOnSubmit(IL);
                db.SubmitChanges();
            }
        }

        public IEnumerable<DTO.Category> GetCategories(int TermID)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                return db.CategoryTerms.Where(x=>x.IDTerm == TermID).Select(x => DTOConverter.Instance.CreateCategory(x)).ToList();
            }
        }

        public IEnumerable<DTO.Department> GetUSTUTree(int TermID)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                return db.DepartmentTerms.Where(x => x.IDTerm == TermID && x.DepartmentTerm1 == null).
                    Select(x => DTOConverter.Instance.CreateDepartmentWithRespondents(x)).ToList();
            }
        }

        public IEnumerable<DTO.Section> GetISTree(int TermID)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var Sec = from S in db.Sections
                          join ST in db.SectionTerms on S.IDSection equals ST.IDSection
                          where (ST.IDTerm == TermID) && (ST.IDParentSectionTerm == null)
                          select ST;

                List<DTO.Section> list = new List<DTO.Section>();

                foreach (SectionTerm S in Sec)
                {
                    list.Add(DTOConverter.Instance.CreateSection(S));
                }

                return list;
            }
        }

        

        public IEnumerable<DTO.Position> GetPositionList()
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var Positions = from P in db.Positions
                                where P.Order != null 
                                select P;

                List<DTO.Position> list = new List<DTO.Position>();
                foreach (var Pos in Positions)
                {
                    list.Add(new DTOConverter().CreatePosition(Pos));
                }

                return list;
            }
        }

        public IEnumerable<NullChar> GetNullableChars(int TermID)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var NotNullChars = db.Answers.Where(x=>x.CharacteristicTerm.IDTerm == TermID).Select(x=>x.CharacteristicTerm).Distinct();
                var NullChars = db.CharacteristicTerms.Where(x => x.IDTerm == TermID && !NotNullChars.Contains(x)).Select(x=>DTOConverter.CreateNullChar(x));
                return NullChars.ToList();
            }
        }

        public IEnumerable<DTO.Money> GetPremiums(int TermID, DTO.IndexStrategyType strategy)
        {
            using (var db = new IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var term = db.Terms.Single(x => x.IDTerm == TermID);
                var c = term.GetIndexContextPPS(); //GetContext(term, strategy);
                var Respondents = term.PremiumParticipants(c);

                List<Money> list = new List<Money>();
                foreach (Respondent Resp in Respondents)
                {
                    list.Add(DTOConverter.Instance.CreateMoneyStub(Resp));
                }

                return list;
            }
        }

        public IEnumerable<DTO.Section> GetParentsSections(int TermID)
        {
            using (IMModel.Domain.IMDataDataContext db = 
                new IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var Sec = from S in db.Sections
                          join ST in db.SectionTerms on S.IDSection equals ST.IDSection
                          where (ST.IDTerm == TermID) && (ST.IDParentSectionTerm == null) && (ST.Order != "7")
                          select ST;

                return Sec.Select(S => DTOConverter.Instance.CreateSection(S)).ToList();
            }
        }

        //private List<CharacteristicTerm> GetExtraCharacteristic(CharacteristicTerm currCharactericTerm)
        //{
        //    using (IMModel.Domain.IMDataDataContext context = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
        //    {
        //        var term = context.Terms.;
        //        var c = new CharacteristicTerm();
        //        while (currCharactericTerm != null)
        //        {
        //            if (currCharactericTerm.IsValidInPeriod(term))
        //            {
        //                c += currCharactericTerm.
        //            }
        //            currCharactericTerm = currCharactericTerm.InsteadOf;
        //        }
        //        return c;
        //    }
        //}



        public IEnumerable<DTO.Reports.VolumsPPS> GetVolumsPPS(int TermID, IEnumerable<int> Persons, IEnumerable<int> Sections, IEnumerable<int> Cats)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                //добавить правильную поттяжку результатов экстра класса

                Term currentTermOrder = db.Terms.Single(x => x.IDTerm == TermID);

                List<DTO.Reports.VolumsPPS> volums = new List<DTO.Reports.VolumsPPS>();

                var respondents = db.Respondents.Where(x => Persons.Contains(x.IDRespondent)).OrderBy(x=>x.Person.LastName).ToList();

                //var TotalCH = (from Ch in db.CharacteristicTerms
                //               where Ch.Term.IDTerm == TermID
                //                     && Sections.Contains(Ch.IDCharacteristicTerm)
                //                     && Cats.Contains(Ch.CategorySectionTerm.CategoryTerm.Category.IDCategory)
                //               select DTOConverter.Instance.CreateCharacteristic(Ch)).ToList();

                var TotalCH = (from Ch in db.CharacteristicTerms
                                 join A in db.Answers on Ch.IDCharacteristicTerm equals A.CharacteristicTerm.IDCharacteristicTerm 
                               where Ch.Term.IDTerm == TermID
                                     && Sections.Contains(Ch.IDCharacteristicTerm)
                                     && Cats.Contains(Ch.CategorySectionTerm.CategoryTerm.Category.IDCategory)
                                  && respondents.Contains(A.Respondent) 
                               select Ch).ToList();


                //var AllUsed = from Ch in db.CharacteristicTerms
                //               where Ch.Term.IDTerm == TermID
                //                     && Sections.Contains(Ch.IDCharacteristicTerm)
                //                     && Cats.Contains(Ch.CategorySectionTerm.CategoryTerm.Category.IDCategory)
                //               select Ch;
                //var exta = from Ch in AllUsed
                //           join CT in db.CharacteristicTerms on Ch.IDPrevCharacteristicTerm equals
                //               CT.IDCharacteristicTerm
                //               where CT.VolumeValidPeriod>1
                //           select CT;


                TotalCH.AddRange((from Ch in db.CharacteristicTerms
                                  where (Ch.VolumeValidPeriod > currentTermOrder.Order - Ch.Term.Order)
                                  && Ch.Term.IDTerm != currentTermOrder.IDTerm
                                  select Ch).ToList());

               

                List<DTO.Section> sections = (from ST in db.SectionTerms
                                              join S in db.Sections on ST.IDSection equals S.IDSection
                                              where ST.Order.Length == 1 && ST.Term.IDTerm==TermID
                                              orderby ST.Order
                                              select DTOConverter.Instance.CreateSection(ST)).ToList();

                var ch = new List<CharacteristicTerm>();

                foreach (var characteristic in TotalCH)
                {
                    if (ch.FirstOrDefault(x => x.IDCharacteristicTerm == characteristic.IDCharacteristicTerm) == null)
                        ch.Add(characteristic);
                }

                var ct = DTOConverter.Instance.CreateTerm(currentTermOrder);

                foreach (var GrChar in ch)
                {
                    IList<DTO.RespondentAnswers> RA = new List<RespondentAnswers>();
                    foreach (var person in Persons)
                    {
                        List<int> pers = new List<int>();
                        pers.Add(person);

                        int PersID = person;
                        if (GrChar.Term.IDTerm != currentTermOrder.IDTerm)
                        {


                            var z = from R in db.Respondents
                                    where R.IDRespondent == person
                                    select R.Person.IDPerson;

                            int id = z.Single();
                            pers = (from R in db.Respondents
                                      join DT in db.DepartmentTerms on R.IDDepartmentTerm equals
                                          DT.IDDepartmentTerm
                                      where (R.Person.IDPerson == id) && (DT.IDTerm == GrChar.IDTerm)
                                      select R.IDRespondent).ToList();

                        }

                        foreach(var i in pers)
                        {
                            var a = GetAnswersByCharAndResp(GrChar.IDCharacteristicTerm, i);
                            if (a.Answers.Count() != 0) RA.Add(a);
                        }
                    } 
                    if (RA.Count == 0) continue;
                    //var vpps = DTOConverter.Instance.CreateV(DTOConverter.Instance.CreateCharacteristic(GrChar), RA, respondents, TermID, GrChar.IDTerm);
                    var vpps = DTOConverter.Instance.CreateV(DTOConverter.Instance.CreateCharacteristic(GrChar), RA, respondents, currentTermOrder, GrChar.Term);

                    
                    vpps.Section = sections.Find(x => x.Order == GrChar.Order[0].ToString());
                    volums.Add(vpps);
                }

                return volums;
            }
        }

        public IEnumerable<DTO.IndexRow> GetLIDIA(int TermID, int[] RespID)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var term = db.Terms.Where(x => x.IDTerm == TermID).Single();
                IMModel.Domain.Common.RespondentIndexCollection result = term.Indexes;

                List<DTO.IndexRow> indexes = new List<IndexRow>();
                foreach (KeyValuePair<IMModel.Domain.Respondent, double> Res in result)
                {
                    if (RespID.Contains(Res.Key.IDRespondent))
                    {
                        DTO.IndexRow Row = DTOConverter.Instance.CreateIndexesStub(Res.Key, Res.Value, IndexListType.Compact);
                        indexes.Add(Row);
                    }
                }

                return indexes;
            }
        }


        public IEnumerable<DTO.Reports.VolumePPS> GetOrderedVolums(int IDCharGradTerm)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var Answers = from A in db.Answers
                              where A.IDCharacteristicTerm == IDCharGradTerm
                              orderby A.Volume descending
                              select A;

                List<DTO.Reports.VolumePPS> list = new List<DTO.Reports.VolumePPS>();

                foreach (Answer A in Answers)
                {
                    list.Add(DTOConverter.Instance.CreateVolumePPS(A));
                }

                return list;
            }
        }
    }

}
